IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = N'EMODE')
BEGIN
EXEC(N'CREATE SCHEMA [EMODE]')
END
IF NOT EXISTS(SELECT * FROM sysobjects where name='ARTICLE_COLOR_LOOKUP' and xtype='U') 
CREATE TABLE [EMODE].[ARTICLE_COLOR_LOOKUP] (
[ARTICLE_CODE] NUMERIC(6,0),
[COLOR_CODE] NUMERIC(4,0),
[ARTICLE_LABEL] varchar(45),
[COLOR_LABEL] varchar(30),
[CATEGORY] varchar(25),
[SALE_PRICE] NUMERIC(38,0),
[FAMILY_NAME] varchar(20),
[FAMILY_CODE] varchar(3)
)
GO

IF NOT EXISTS(SELECT * FROM sysobjects where name='ARTICLE_LOOKUP' and xtype='U')
CREATE TABLE [EMODE].[ARTICLE_LOOKUP] (
[ARTICLE_CODE] NUMERIC(6,0),
[ARTICLE_LABEL] varchar(45),
[CATEGORY] varchar(25),
[SALE_PRICE] NUMERIC(38,0),
[FAMILY_NAME] varchar(20),
[FAMILY_CODE] varchar(3)
)
GO

IF NOT EXISTS(SELECT * FROM sysobjects where name='CALENDAR_YEAR_LOOKUP' and xtype='U')
CREATE TABLE [EMODE].[CALENDAR_YEAR_LOOKUP] (
[WEEK_KEY] NUMERIC(3,0),
[WEEK_IN_YEAR] NUMERIC(2,0),
[YEAR] NUMERIC(4,0),
[FISCAL_PERIOD] varchar(4),
[YEAR_WEEK] varchar(7),
[QUARTER] NUMERIC(1,0),
[MONTH_NAME] varchar(10),
[MONTH] NUMERIC(2,0),
[HOLIDAY_FLAG] varchar(1)
)
GO

IF NOT EXISTS(SELECT * FROM sysobjects where name='OUTLET_LOOKUP' and xtype='U')
CREATE TABLE [EMODE].[OUTLET_LOOKUP] (
[SHOP_NAME] varchar(30),
[ADDRESS_1] varchar(20),
[MANAGER] varchar(10),
[DATE_OPEN] date,
[OPEN] varchar(1),
[OWNED_OUTRIGHT] varchar(1),
[FLOOR_SPACE] NUMERIC(4,0),
[ZIP_CODE] varchar(6),
[CITY] varchar(20),
[STATE] varchar(20),
[SHOP_CODE] NUMERIC(3,0)
)
GO
IF NOT EXISTS(SELECT * FROM sysobjects where name='SHOP_FACTS' and xtype='U')
CREATE TABLE [EMODE].[SHOP_FACTS] (
[ID] NUMERIC(5,0),
[ARTICLE_CODE] NUMERIC(6,0),
[COLOR_CODE] NUMERIC(4,0),
[WEEK_KEY] NUMERIC(3,0),
[SHOP_CODE] NUMERIC(4,0),
[MARGIN] NUMERIC(38,0),
[AMOUNT_SOLD] NUMERIC(38,0),
[QUANTITY_SOLD] NUMERIC(38,0)
)
GO


ALTER TABLE SHOP_FACTS NOCHECK CONSTRAINT ALL;
ALTER TABLE shop_facts NOCHECK CONSTRAINT ALL;
ALTER TABLE  ARTICLE_COLOR_LOOKUP NOCHECK CONSTRAINT ALL;
ALTER TABLE  article_lookup NOCHECK CONSTRAINT ALL;
ALTER TABLE  calendar_year_lookup NOCHECK CONSTRAINT ALL;
ALTER TABLE  outlet_lookup NOCHECK CONSTRAINT ALL;


delete from shop_facts;
delete from ARTICLE_COLOR_LOOKUP;
delete from article_lookup;
delete from calendar_year_lookup;
delete from outlet_lookup;
delete from AGG_YR_FP_YW_TI_RV_MA_QT;
delete from AGG_YR_FP_YW_MO_TI_RV_MA_QT;
